package com.academic_system.mapper;

import com.academic_system.model.MarkCondition;
import com.academic_system.model.MarkCount;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author 20155884 王晓阳
 * @className : MarkDao
 * @Description
 **/
public interface MarkDao {
    /**
     * @param
     * @Description 查询课程和mark为空的学生id
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     **/
    @Select("select * from mark,course,student where course_name=#{courseName} " +
            "and mark.mark is null and mark.course_id=course.course_id and mark.stu_Info_id=student.stu_Info_id")
    List<MarkCondition> findStuIdByMark(@Param("courseName") String courseName);

    /**
     * @param markCondition
     * @author 20155884 王晓阳
     * @methodName :selectMark
     * @Description 动态查询成绩。课程号,课程名称,课程类型,学生id,学期id任两个组合进行查询
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "selectMark")
    List<MarkCondition> selectMark(MarkCondition markCondition);

    /**
     * @param term_id
     * @Description 在courseterm表中按学期查询本学期所有课程的总学分
     * @author 20155884 王晓阳
     * @methodName findTotalCourseCredithour
     * @returnType Msg
     **/
    @Select("select sum(course_credithour) from course_term,course where course_term.course_id=course.course_id and term_id=#{term_id}")
    int findTotalCourseCredithour(@Param("term_id") int term_id);

    /**
     * @param markCondition
     * @Description 查询学生的总学分
     * @author 20155884 王晓阳
     * @methodName findStuCredithour
     * @returnType Msg
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "findStuCredithour1")
    MarkCondition findStuCredithour1(MarkCondition markCondition);

    /**
     * @param
     * @Description 成绩录入
     * @author 20155884 王晓阳
     * @methodName addMark
     * @returnType int
     **/
    @Insert("insert into mark(mark_id,course_id,term_id,stu_info_id,mark,mark_gradepoint) " +
            "values(NULL,#{courseId},#{termId},#{stuInfoId},#{mark},#{markGradepoint})")
    int addMark(MarkCondition markCondition);

    /**
     * @param markCondition
     * @Description 通过成绩id修改成绩------
     * @author 20155884 王晓阳
     * @methodName updateMarkByMarkId
     * @returnType int
     **/
    @Update("update mark set " +
            "mark=#{markCondition.mark}," +
            "mark_gradepoint=#{markCondition.markGradepoint} " +
            "where mark_id = #{markCondition.markId}")
    int updateMarkByMarkId(@Param("markCondition") MarkCondition markCondition);

    /**
     * @param markCondition
     * @Description 查询不及格成绩的个数
     * @author 20155884 王晓阳
     * @methodName findFailGradeCount
     * @returnType int
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "findFailGradeCount")
    int findFailGradeCount(MarkCondition markCondition);

    /**
     * @param markCondition
     * @Description 动态查询不及格成绩按学科查，按学号查，按学期查
     * @author 20155884 王晓阳
     * @methodName selectFallGrade
     * @returnType List
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "selectFallGrade")
    List<MarkCondition> selectFallGrade(MarkCondition markCondition);

    /**
     * @param
     * @Description 按学期和课程id查询还没有录成绩的学生id，即查询没有录成绩的学生个数
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     **/
    @Select("select DISTINCT mark.stu_info_id from student INNER join mark on student.stu_info_id=mark.stu_info_id " +
            "NOT IN (SELECT stu_info_id FROM mark WHERE mark.course_id=#{markCondition.courseId} and mark.term_id=#{markCondition.termId} )")
    List<MarkCondition> queryNoScoreStudent(@Param("markCondition") MarkCondition markCondition);

    /**
     * @param
     * @Description 根据学期id，courseid coursename 查询课程
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "findCourseTerm")
    List<MarkCondition> findCourseTerm(MarkCondition markCondition);

    /**
     * @param
     * @Description 更新courseterm状态
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     **/
    @Update("update course_term " +
            "set state= #{markCondition.state} where course_id=#{markCondition.courseId} and term_id=#{markCondition.termId}")
    int updateCoursetermState(@Param("markCondition") MarkCondition markCondition);

    /**
     * @param
     * @Description 查询各分数段的学生人数
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     **/
    @SelectProvider(type = MarkSqlProvider.class, method = "selectMarkCount")
    MarkCount selectMarkCount(MarkCount markCount);
}
